package com.base.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

import com.base.bean.Cardinfo;
import com.base.bean.Cardmake;
import com.base.bean.Cardstuff;
import com.base.bean.Design;
import com.base.bean.Goodsman;
import com.base.service.CardinfoService;
import com.common.bean.Datadict;
import com.common.bean.Department;
import com.common.util.AbstractManager;
import com.common.util.PagerModel;
import com.common.util.StringToken;

public class CardinfoServiceImpl extends AbstractManager implements
		CardinfoService {

	public void addCardinfo(Cardinfo cardinfo) {
		String hql = "select nvl(max(cardid),0) from Cardinfo";
		
//		String cardno = "";
		Long cardid = (Long) this.getHibernateTemplate().find(hql).get(0);
		cardid += 1l;
		
//		Long num = Long.parseLong(DateHelper.dateToStr(new Date(), "yyyyMMddHms"));//13位
//		num *= 1000l;
//		num += cardid % 1000;
//		cardno = num + "";
		
		cardinfo.setCardid(cardid);//自增序列号
//		cardinfo.setCardno(cardno);//卡编号 时间+自增序列号
		this.getHibernateTemplate().save(cardinfo);
	}

	@SuppressWarnings("unchecked")
	public List<Cardinfo> findAllCardinfo() {
		String hql = "from Cardinfo order by cardno";
		return this.getHibernateTemplate().find(hql);
	}

	public Cardinfo findCardinfo(String cardno) {
		return (Cardinfo) this.getHibernateTemplate().load(Cardinfo.class, cardno);
	}

	public PagerModel findCardinfoForPager() {
		String hql = "from Cardinfo order by cardno";
		return searchPaginated(hql);
	}

	public void removeCardinfo(String cardno) {
		Cardinfo cardinfo = null;
		cardinfo = (Cardinfo) this.getHibernateTemplate().load(Cardinfo.class, cardno);
		if(cardinfo != null) 
			this.getHibernateTemplate().delete(cardinfo);
	}

	public void updateCardinfo(Cardinfo cardinfo) {
		this.getHibernateTemplate().update(cardinfo);
	}

	public PagerModel findCardinfoForPagerWith(long datadictByBasetypeid, long datadictByAreatypeid,
			long datadictByFuntypeid, long datadictByStatustypeid, long money,
			long cardsubtypeno, String deptcode) {
		String hql = "from Cardinfo ci where ";
		Object[] objArr = new Object[7];
		int i = 0;//指针
		
		if(datadictByBasetypeid != 0) {
			if(i != 0 ) {
				hql += " and ";
			}
			hql += " ci.datadictByBasetype.typeid = ? ";
			objArr[i] = datadictByBasetypeid;
			i += 1;
		}
		if(datadictByAreatypeid != 0) {
			if(i != 0 ) {
				hql += " and ";
			}
			hql += " ci.datadictByAreatype.typeid = ? ";
			objArr[i] = datadictByAreatypeid;
			i += 1;
		}
		if(datadictByFuntypeid != 0) {
			if(i != 0 ) {
				hql += " and ";
			}
			hql += " ci.datadictByFuntype.typeid = ? ";
			objArr[i] = datadictByFuntypeid;
			i += 1;
		}
		if(datadictByStatustypeid != 0) {
			if(i != 0 ) {
				hql += " and ";
			}
			hql += " ci.datadictByStatus.typeid = ? ";
			objArr[i] = datadictByStatustypeid;
			i += 1;
		}
		if(money != 0) {
			if(i != 0 ) {
				hql += " and ";
			}
			hql += " ci.money = ? ";
			objArr[i] = money;
			i += 1;
		}
		if(cardsubtypeno != 0) {
			if(i != 0 ) {
				hql += " and ";
			}
			hql += " ci.cardmake.cardseries.cardsubtype.cardsubtypeno = ? ";
			objArr[i] = cardsubtypeno;
			i += 1;
		}
		if(!"".equals(deptcode)) {
			if(i != 0 ) {
				hql += " and ";
			}
			hql += " ci.department.deptcode = ?";
			objArr[i] = deptcode;
			i += 1;
		}
		
		if(i == 0 ){
			hql = "from Cardinfo ci ";
			return searchPaginated(hql);
		}
		
		Object[] objTemp = new Object[i];
		
		for(int j = i-1;j>=0;j--) {
			objTemp[j] = objArr[j];
		}
		
		return searchPaginated(hql, objTemp);
	}

	public PagerModel findCardinfoForPagerWithBelongto(String belongto,
			long datadictByStatustypeid, long cardsubtypeno, String deptcode) {
		String hql = "from Cardinfo ci where ci.belongto = ? and ci.datadictByStatus.typeid = ? " +
			"and ci.cardmake.cardseries.cardsubtype.cardsubtypeno = ? and ci.department.deptcode = ?";
		return searchPaginated(hql, new Object[]{belongto,datadictByStatustypeid,cardsubtypeno,deptcode});
	}
	
	public PagerModel findCardinfoForPagerWithBelongto(String belongto,long datadictByStatustypeid,String deptcode) {
		String hql = "from Cardinfo ci where ci.belongto = ? and ci.datadictByStatus.typeid = ? " +
		"and ci.department.deptcode = ?";
		return searchPaginated(hql, new Object[]{belongto,datadictByStatustypeid,deptcode});
	}

	public void addCardinfoFromExcel(File file) {
		try{
			// 构建Workbook对象, 只读Workbook对象
			// 直接从本地文件创建Workbook
			// 从输入流创建Workbook
			InputStream is = new FileInputStream(file);
			jxl.Workbook rwb = Workbook.getWorkbook(is);
			// 获取第一张Sheet表 要求卡信息所在文件的位置符号
			Sheet rs = rwb.getSheet(0);
			// 将表中单元格数据转化成cardinfo中字段值，返回cardinfo list对象
			for (int i = 1; i < rs.getRows(); i++) {//i=1 第二行开始 第一行一般是表头
				Cardinfo cardinfo = new Cardinfo();
				cardinfo.setCardno(((LabelCell) rs.getCell(0, i)).getString());
				cardinfo.setCovercaption(((LabelCell) rs.getCell(1, i)).getString());
				cardinfo.setPassword(((LabelCell) rs.getCell(2, i)).getString());
				cardinfo.setUnit(((LabelCell) rs.getCell(3, i)).getString());
				cardinfo.setMoney((long)(((NumberCell) rs.getCell(4, i)).getValue()));
				cardinfo.setMoneyex((long)(((NumberCell) rs.getCell(5, i)).getValue()));
				cardinfo.setValiddate(formateDate(rs.getCell(6, i)));
				
				Datadict datadictByStatus = new Datadict();
				Department department = new Department();
				Design design = new Design();
				Cardstuff cardstuff = new Cardstuff();
				Goodsman goodsman = new Goodsman();
				Cardmake cardmake = new Cardmake();
				Datadict datadictByAreatype = new Datadict();
				Datadict datadictByBasetype = new Datadict();
				Datadict datadictByFuntype = new Datadict();
				
				datadictByStatus.setTypeid(StringToken.DICT_CARDSTATUS_INIT);
				department.setDeptcode(StringToken.DEPARTMENT_CARD_DEF);
				design.setDesignno((long)(((NumberCell) rs.getCell(7, i)).getValue()));
				cardstuff.setStuffno((long)(((NumberCell) rs.getCell(8, i)).getValue()));
				goodsman.setGoodmanno((long)(((NumberCell) rs.getCell(9, i)).getValue()));
				cardmake.setMakeno((long)(((NumberCell) rs.getCell(10, i)).getValue()));
				datadictByBasetype.setTypeid((long)(((NumberCell) rs.getCell(11, i)).getValue()));
				datadictByAreatype.setTypeid((long)(((NumberCell) rs.getCell(12, i)).getValue()));
				datadictByFuntype.setTypeid((long)(((NumberCell) rs.getCell(13, i)).getValue()));
				
				cardinfo.setDatadictByStatus(datadictByStatus);//初始状态 默认不需要提供
				cardinfo.setDepartment(department);//组织架构 默认不需要提供
				cardinfo.setDesign(design);// 7 
				cardinfo.setCardstuff(cardstuff);// 8
				cardinfo.setGoodsman(goodsman);// 9
				cardinfo.setGoodsdate(new Date()); // 默认当前时间
				cardinfo.setCardmake(cardmake); // 10
				cardinfo.setBelongto(""); // 初始为空 默认不需要提供
				
				cardinfo.setDatadictByBasetype(datadictByBasetype); // 11
				cardinfo.setDatadictByAreatype(datadictByAreatype); // 12
				cardinfo.setDatadictByFuntype(datadictByFuntype); // 13
				
				addCardinfo(cardinfo);//调用上面的方法保存对象
			}
			// 操作完成时，关闭对象，释放占用的内存空间
			rwb.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}

	private Date formateDate(Cell formatecell) {
		try {
			java.util.Date mydate = null;
			DateCell datecll = (DateCell) formatecell;
			mydate = datecll.getDate();
			long time = (mydate.getTime() / 1000) - 60 * 60 * 8;
			mydate.setTime(time * 1000);
			return mydate;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}
